package com.threeti.mecool.core.application.basicdata;

import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.threeti.mecool.core.domain.model.basicdata.AdminDivision;
import com.threeti.mecool.core.domain.model.basicdata.AdminDivisionLevelDeep;
import com.threeti.mecool.core.domain.model.basicdata.Brand;
import com.threeti.mecool.core.domain.model.basicdata.BrandCategory;
import com.threeti.mecool.core.domain.model.basicdata.Customer;
import com.threeti.mecool.core.domain.model.basicdata.Product;
import com.threeti.mecool.core.domain.model.basicdata.biz.Channel;
import com.threeti.mecool.core.domain.model.basicdata.biz.ChannelCategory;
import com.threeti.mecool.core.domain.model.basicdata.biz.ChannelSysCategory;
import com.threeti.mecool.core.domain.model.basicdata.biz.MeCoolAdminDivisionAreaRegistry;
import com.threeti.mecool.core.util.TreeHelper;
import com.threeti.mecool.framework.util.Lists;
import com.threeti.mecool.framework.util.Sets;
import com.threeti.mecool.legacy.application.ProductLegacyService;
import com.threeti.mecool.legacy.domain.model.basicdata.AdminDivisionLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.BrandLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.CustomerLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.MDBrand;
import com.threeti.mecool.legacy.domain.model.basicdata.MDCategory;
import com.threeti.mecool.legacy.domain.model.basicdata.MDCustomer;
import com.threeti.mecool.legacy.domain.model.basicdata.MDProduct;
import com.threeti.mecool.legacy.domain.model.basicdata.ProductLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.biz.PosKindLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.biz.PosLegacy;
import com.threeti.mecool.legacy.domain.model.basicdata.biz.PosSysTypeLegacy;
import com.threeti.mecool.legacy.infrastructure.persistence.AdminDivisionLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.BrandCategoryLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.BrandLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.CustomerLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.PosKindLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.PosLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.PosSysTypeLegacyRepository;
import com.threeti.mecool.legacy.infrastructure.persistence.ProductLegacyRepository;

@Service
public class BasicDataServiceImpl implements BasicDataService {
	
    public static final Logger logger = LoggerFactory.getLogger(BasicDataServiceImpl.class);

    @Autowired
    private AdminDivisionLegacyRepository adminDivLegacyRepo;

    @Autowired
    private PosKindLegacyRepository posKindLegacyRepo;

    @Autowired
    private PosLegacyRepository posLegacyRepo;

    @Autowired
    private PosSysTypeLegacyRepository posSysTypeLegacyRepository;

    @Autowired
    private CustomerLegacyRepository customerLegacyRepo;

    @Autowired
    private ProductLegacyRepository productLegacyRepo;

    @Autowired
    private BrandLegacyRepository brandLegacyRepo;

    @Autowired
    private BrandCategoryLegacyRepository brandCategoryLegacyRepo;

    @Autowired
    private ProductLegacyService prodLegService;

    @PersistenceContext
    private EntityManager em;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<AdminDivision> findAdminDivisionsByParentProvinceAdNameAndLevelDeep(String parentProvinceAdName, String parentAdCode, AdminDivisionLevelDeep levelDeep) {
        List<AdminDivisionLegacy> adminDivLegCandidates =
                adminDivLegacyRepo.findByParentProvinceAdNameAndDeepOrderByCode(parentProvinceAdName, levelDeep.ordinal());

        List<AdminDivision> resultNodes = TreeHelper.templateBuildTree(adminDivLegCandidates, AdminDivision.class, adminDivLegacyRepo, parentAdCode);
        return resultNodes;
    }

    @Override
    public List<AdminDivision> findAdminDivisionsByParentCityAdNameAndAdCode(String parentCityAdName, String parentAdCode) {
        List<AdminDivisionLegacy> adminDivLegCandidates = adminDivLegacyRepo.findByParentCityAdNameOrderByCode(parentCityAdName);

        //jay:do the filter,just to avoid duplicate ParentCityAdName in the database->
        List<AdminDivisionLegacy> targetAdminDivLegs = Lists.newArrayList();
        for (AdminDivisionLegacy adminDivLeg : adminDivLegCandidates) {
            if (adminDivLeg.getDeep() < AdminDivisionLevelDeep.CITY.ordinal()) {
                //throw new RuntimeException("不支持的行政区划Level，parentCityAdName和parentAdCode对应的行政区划Level必须 >= AdminDivisionLevelDeep.PROVINCE级");
            }
            if (parentAdCode.equals(adminDivLeg.getParentId())) {
                targetAdminDivLegs.add(adminDivLeg);
            }
        }
        if (CollectionUtils.isEmpty(targetAdminDivLegs)) {
            return Lists.newEmptyArrayList();
        }
        //<-

        List<AdminDivision> resultNodes = TreeHelper.templateBuildTree(targetAdminDivLegs, AdminDivision.class, adminDivLegacyRepo, parentAdCode);
        return resultNodes;
    }

    @Override
    public List<AdminDivision> findAdminDivisionsByLevelDeep(AdminDivisionLevelDeep levelDeep) {
        List<AdminDivisionLegacy> adminDivLegs;
        if (levelDeep == AdminDivisionLevelDeep.NATIONWIDE) {
            adminDivLegs = adminDivLegacyRepo.findAll();
        } else {
            adminDivLegs = adminDivLegacyRepo.findByDeep(levelDeep.ordinal());
        }

        List<AdminDivision> resultNodes = TreeHelper.templateBuildTree(adminDivLegs, AdminDivision.class, adminDivLegacyRepo);
        return resultNodes;
    }

    @Override
    public List<AdminDivision> findAllAdminDivision() {
        List<AdminDivisionLegacy> adminDivLegs = adminDivLegacyRepo.findAll();

        List<AdminDivision> resultNodes = TreeHelper.templateBuildTree(adminDivLegs, AdminDivision.class, adminDivLegacyRepo);
        return resultNodes;
    }

    @Override
    @Deprecated
    public Page<Channel> findChannels(Pageable pageable) {
        Page<PosLegacy> poses = posLegacyRepo.findAll(pageable);

        List<Channel> channels = Lists.newArrayList();
        for (PosLegacy posLeg : poses) {
            PosKindLegacy posKindLegacy = posKindLegacyRepo.findOne(posLeg.getKindCode());
            Channel channel = new Channel(posLeg.getCode(), posLeg.getName(), posLeg.getAddress(),
                    posKindLegacy.getId(),
                    posLeg.getSysKind(), posLeg.getAdId());
            channels.add(channel);
        }
        return new PageImpl<Channel>(channels, pageable, poses.getTotalElements());
    }

    @Override
    public Channel preProcessChannelBy(String channelCode) {
        PosLegacy posLeg = posLegacyRepo.findOne(channelCode);
        Channel channel = new Channel(posLeg.getCode(), posLeg.getName(), posLeg.getAddress(), posLeg.getKindCode(), posLeg.getSysKind(), posLeg.getAdId());

        AdminDivisionLegacy adminDivLeg = null;
        try {
            adminDivLeg = adminDivLegacyRepo.findById(posLeg.getAdId());
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        if (adminDivLeg == null) {
            logger.warn("无法获取通路的行政地域信息，这会降低报表的统计精度");
            return channel;
        }

        //城市区域级数据录入和校正->
        channel.setAdName(adminDivLeg.getName());
        if (MeCoolAdminDivisionAreaRegistry.isAreaNameSpecial(channel.getAdName())) {
            channel.setAdName(adminDivLeg.getParentCityAdName());
            if (StringUtils.isBlank(channel.getAdName())) {
                channel.setAdName(adminDivLeg.getParentProvinceAdName());
            }
        }

        //TODO jay:假设经过校正后的channelAdName不会重复，但这个假设比较脆弱
        AdminDivisionLegacy tmpForAreaCode = null;
        try {
            tmpForAreaCode = adminDivLegacyRepo.findByName(channel.getAdName());
        } catch (Exception e) {
            logger.warn("地理区域的AdName[" + channel.getAdName() + "]发现重复，将启用AdId进行查询", e);
        }
        if (tmpForAreaCode == null) {
            tmpForAreaCode = adminDivLegacyRepo.findById(channel.getAdminDivId());
        }
        if (tmpForAreaCode != null) {
            channel.setAdCode(tmpForAreaCode.getCode());
        }
        //<-

        //省级数据录入和校正->
        if (StringUtils.isNotBlank(adminDivLeg.getParentProvinceAdName())) {
            channel.setProvinceAdName(adminDivLeg.getParentProvinceAdName());
        } else if (StringUtils.isNotBlank(adminDivLeg.getCode()) && adminDivLeg.getCode().endsWith("000000")) {//表示是"省"
            channel.setProvinceAdName(adminDivLeg.getName());
        } else {
            //做一次强制性地补偿查询，以保证取出正确的省份数据
            AdminDivisionLegacy adminDivLegParent = adminDivLegacyRepo.findOne(adminDivLeg.getParentId());
            channel.setProvinceAdName(adminDivLegParent.getName());
        }

        tmpForAreaCode = adminDivLegacyRepo.findByName(channel.getProvinceAdName());
        channel.setProvinceAdCode(tmpForAreaCode.getCode());
        //<-

        //城市级数据录入和校正->
        if (StringUtils.isNotBlank(adminDivLeg.getParentCityAdName())) {
            channel.setCityAdName(adminDivLeg.getParentCityAdName());

            //某些直辖市，如北京市、上海市，直接启用它们的省名作为城市名，因为它们同名
            //来自于原始遗留数据的通路本就未进行正确的最明细地关联，比如终端明明位于苏州市市辖区的沧浪区，但只关联到了苏州市市辖区这个层次,遇到这种情况，只能启用苏州市作为城市名
            if (channel.getCityAdName().indexOf("市辖区") != -1) {
                if (channel.getCityAdName().equals("市辖区")) {
                    channel.setCityAdName(channel.getProvinceAdName());
                } else {//xxx市市辖区，去除市辖区后三个字
                    channel.setCityAdName(channel.getCityAdName().replaceAll("市辖区", ""));
                }
            } else if (channel.getCityAdName().indexOf("县") != -1) {
                if (channel.getCityAdName().equals("县")) {
                    channel.setCityAdName(channel.getProvinceAdName());
                } else {//如，省直辖县级行政单位,直辖县级行政单位
                    channel.setCityAdName(channel.getAdName());
                }
            }
        } else {
            //包含'(市)'这种特殊情况，如:重庆市(市)
            //某些直辖市，如北京市、上海市，直接启用它们的省名作为城市名，因为它们同名
            channel.setCityAdName(adminDivLeg.getParentProvinceAdName());
        }

        tmpForAreaCode = adminDivLegacyRepo.findByName(channel.getCityAdName());
        channel.setCityAdCode(tmpForAreaCode.getCode());
        //<-

        return channel;
    }

    @Override
    public List<ChannelCategory> findAllChannelCategories() {
        List<PosKindLegacy> posKindLegs = posKindLegacyRepo.findAll();
        List<ChannelCategory> resultNodes = TreeHelper.templateBuildTree(posKindLegs, ChannelCategory.class, posKindLegacyRepo);
        return resultNodes;
    }

    @Override
    public List<ChannelSysCategory> findAllChannelSysCategories() {
        List<PosSysTypeLegacy> posSysTypes = posSysTypeLegacyRepository.findAll();

        List<ChannelSysCategory> channelSysCategories = Lists.newArrayList();
        for (PosSysTypeLegacy posSysTypeLegacy : posSysTypes) {
            PosKindLegacy posKindLegacy = posKindLegacyRepo.findOne(posSysTypeLegacy.getPosKindCode());
            channelSysCategories.add(new ChannelSysCategory(posSysTypeLegacy.getCode(), posSysTypeLegacy.getName(),
                    new ChannelCategory(posKindLegacy.getId(), posKindLegacy.getName())));
        }
        return channelSysCategories;
    }

    @Override
    public List<Customer> findAllCustomers() {
        List<CustomerLegacy> customerLegs = customerLegacyRepo.findAll();
        List<Customer> customers = Lists.newArrayList();
        for (CustomerLegacy customerLegacy : customerLegs) {
            customers.add(new Customer(customerLegacy.getCode(), customerLegacy.getName()));
        }
        return customers;
    }

    @Override
    public Set<BrandCategory> findBrandCategoriesByCustomer(String customerCode) {
        List<BrandLegacy> brandLegs = brandLegacyRepo.findByCustomerCode(customerCode);
        Set<BrandCategory> brandCats = new TreeSet<BrandCategory>(new Comparator<BrandCategory>() {
            @Override
            public int compare(BrandCategory o1, BrandCategory o2) {
                return o1.getCode().compareTo(o2.getCode());
            }
        });
        for (BrandLegacy brandLeg : brandLegs) {
            brandCats.add(new BrandCategory(brandLeg.getCategoryCode()));
        }

        for (BrandCategory brandCategory : brandCats) {
            brandCategory.setName(brandCategoryLegacyRepo.findOne(brandCategory.getCode()).getName());
        }
        return brandCats;
    }

    @Override
    public List<Brand> findBrandsByCustomerAndBrandCateogry(String customerCode, String brandCategoryCode) {
        List<BrandLegacy> brandLegs = brandLegacyRepo.findByCustomerCodeAndCategoryCode(customerCode, brandCategoryCode);
        List<Brand> brands = Lists.newArrayList();
        for (BrandLegacy brandLeg : brandLegs) {
            brands.add(new Brand(brandLeg.getCode(), brandLeg.getName(), brandLeg.getCategoryCode()));//jay:without customer fetching,just aim at display...
        }
        return brands;
    }

    @Override
    public List<Product> findProductsByBrandCategoryAndBrand(String brandCategoryCode, String brandCode) {
        List<ProductLegacy> prodLegs = prodLegService.findByBrandCategoryCodeAndBrandCode(brandCategoryCode, brandCode);

        List<Product> products = Lists.newArrayList();
        for (ProductLegacy productLegacy : prodLegs) {
            products.add(new Product(productLegacy.getCode(), productLegacy.getSkuName(),
                    new Brand(productLegacy.getBrandCode(), productLegacy.getBrandCategoryCode()),
                    new BrandCategory(productLegacy.getBrandCategoryCode())));//jay:without brand and brandcategory name fetching,just aim at display...
        }
        return products;
    }

    @Override
    @Deprecated
    public Set<Product> findAllProductsByCustomerCode(String customerCode) {
        List<BrandLegacy> brandLegs = brandLegacyRepo.findByCustomerCode(customerCode);
        Set<Product> products = Sets.newLinkedHashSet();

        for (BrandLegacy brandLegacy : brandLegs) {
            List<ProductLegacy> productLegs = productLegacyRepo.findByBrandCode(brandLegacy.getCode());
            for (ProductLegacy productLegacy : productLegs) {
                BrandLegacy brandLeg = brandLegacyRepo.findOne(productLegacy.getBrandCode());
                products.add(new Product(productLegacy.getCode(), productLegacy.getCode(), new Brand(brandLeg.getCode())));
            }
        }
        return products;
    }

    @Override
    public MDCustomer findMDCustomerByCustomerLegacy(CustomerLegacy customerLegacy) {
        if (customerLegacy == null) {
            return new MDCustomer("0", "未设置");
        }
        MDCustomer customer = new MDCustomer(customerLegacy.getCode(), customerLegacy.getName());
        Query query;
        Map<String, MDCategory> categoryMap;
        //<editor-fold desc="fill all categories">
        query = em.createNativeQuery("select cat_code, cat_alias, memo from md_cat order by cat_alias");
        List<Object[]> rawCategoryList = query.getResultList();
        categoryMap = new LinkedHashMap<String, MDCategory>(rawCategoryList.size() + 2, 1);
        for (Object[] rawCategory : rawCategoryList) {
            String categoryCode = (String) rawCategory[0];
            String categoryAlias = (String) rawCategory[1];
            String categoryMemo = (String) rawCategory[2];
            categoryMap.put(categoryCode, new MDCategory(categoryCode, categoryAlias, categoryMemo, customer));
        }
        //</editor-fold>
        Map<String, MDBrand> brandMap;
        //<editor-fold desc="fill assigned brands">
        query = em.createNativeQuery("select brand_code, brand_name, brand_cat_code from md_brand where cust_code='" + customer.getCode() + "' order by brand_name");
        List<Object[]> rawBrandList = query.getResultList();
        brandMap = new LinkedHashMap<String, MDBrand>(rawBrandList.size() + 2, 1);
        for (Object[] rawBrand : rawBrandList) {
            String brandCode = (String) rawBrand[0];
            String brandName = (String) rawBrand[1];
            String categoryCode = (String) rawBrand[2];
            MDCategory category = categoryMap.get(categoryCode);
            if (category != null) {
                brandMap.put(brandCode, new MDBrand(brandCode, brandName, category));
            }
        }
        //</editor-fold>
        //<editor-fold desc="fill product">
        StringBuilder brandCodes = new StringBuilder(200);
        brandCodes.append("'__non_exist__'");
        for (String brandCode : brandMap.keySet()) {
            brandCodes.append(",'").append(brandCode).append("'");
        }
        StringBuilder sql = new StringBuilder(200);
        sql.append("select brand_code, article_code, article_sku from md_article where brand_code in (").append(brandCodes.toString()).append(") order by article_sku");
        query = em.createNativeQuery(sql.toString());
        List<Object[]> rawProductList = query.getResultList();
        for (Object[] rawProduct : rawProductList) {
            String brandCode = (String) rawProduct[0];
            String productCode = (String) rawProduct[1];
            String productSku = (String) rawProduct[2];
            MDBrand brand = brandMap.get(brandCode);
            if (brand != null) {
                new MDProduct(productCode, productSku, brand);
            }
        }
        //</editor-fold>
        return customer;
    }

	@Override
	public List<Map<String, Object>> findAllPosKindListByTreeFormat() {
		
		String sql="select * from (select rpad(' ', 2 * (level - 1), '---') || t.pos_kind_name \"NAME\", "
		            +" t.pos_kind_code as id, "
		            +"   connect_by_root t.pos_kind_name \"ROOT_POS_KIND_NAME\", "
		            +"  connect_by_isleaf \"ISLEAF\", "
		            +"  level, "
		            +"  sys_connect_by_path(t.pos_kind_name, '/') \"PATH\"  "
		            +"  from MD_POS_KIND t "
		            +"  start with t.p_pos_kind_code is null "
		            +"  connect by prior t.pos_kind_code = t.p_pos_kind_code) where id!=0 ";
		
		return jdbcTemplate.queryForList(sql);
	}
}
